﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using UniversityManagementSystem.DAL;

namespace UniversityManagementSystem.Gateway
{
    [Serializable]
    public class StudentGateway : GatewayConnection
    {
        private string commandString = null;
        [NonSerialized]
        private SqlDataReader getData = null;


       /* public void SaveStudentInformation(Student aStudent)
        {
            try
            {
                connection.Open();
                string insertQuery = String.Format("INSERT INTO t_studentRegistrationInformation values(@name, @email, @department, @contactNo, @registrationDate, @address)");
                command.CommandText = insertQuery;
                command.Parameters.Clear();
                command.Parameters.Add("@name", SqlDbType.VarChar);
                command.Parameters["@name"].Value = aStudent.name;
                command.Parameters.Add("@email", SqlDbType.VarChar);
                command.Parameters["@email"].Value = aStudent.email;
                command.Parameters.Add("@department", SqlDbType.VarChar);
                command.Parameters["@department"].Value = aStudent.departmentName;
                command.Parameters.Add("@contactNo", SqlDbType.VarChar);
                command.Parameters["@contactNo"].Value = aStudent.contactNo;
                command.Parameters.Add("@registrationDate", SqlDbType.VarChar);
                command.Parameters["@registrationDate"].Value = aStudent.registrationDate;
                command.Parameters.Add("@address", SqlDbType.VarChar);
                command.Parameters["@address"].Value = aStudent.address;
                command.ExecuteNonQuery();
            }
            catch(Exception anException)
            {
                throw anException;
            }
            finally
            {
                connection.Close(); 
            }
        }*/


        public bool GetUniqueKey(string randomNumber)
        {
            bool unique = false;
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT * FROM t_Student WHERE registration_no = @registrationNo");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@registrationNo", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@registrationNo"].Value = randomNumber;
                getData = GetSqlCommand.ExecuteReader();
                if(getData.Read())
                {
                    unique = true;
                }
                GetSqlConn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return unique;
        }

        public bool CheckEmail(string email)
        {
            bool uniqueEmail = false;
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT * FROM t_Student WHERE student_email = @studentEmail");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@studentEmail", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@studentEmail"].Value = email;
                getData = GetSqlCommand.ExecuteReader();
                if (getData.Read())
                {
                    uniqueEmail = true;
                }
                GetSqlConn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return uniqueEmail;
        }

        public bool SaveAstudent(string registrationNo, Student aStudent, List<StudentEducation> aStudentEducation)
        {
            bool saveDoneInformation = false;
            bool saveDoneEducation = false;
            int saved = 0;
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("INSERT INTO t_Student(registration_no,student_name,student_email,student_contact,student_address,student_department,registration_date)VALUES(@registrationNo,@studentName,@studentEmail,@studentContact,@studentAddress,@studentDepartment,@registrationDate)");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@registrationNo", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@registrationNo"].Value = registrationNo;
                GetSqlCommand.Parameters.Add("@studentName", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@studentName"].Value = aStudent.name;
                GetSqlCommand.Parameters.Add("@studentEmail", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@studentEmail"].Value = aStudent.email;
                GetSqlCommand.Parameters.Add("@studentContact", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@studentContact"].Value = aStudent.contactNo;
                GetSqlCommand.Parameters.Add("@studentAddress", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@studentAddress"].Value = aStudent.address;
                GetSqlCommand.Parameters.Add("@studentDepartment", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@studentDepartment"].Value = aStudent.departmentName;
                GetSqlCommand.Parameters.Add("@registrationDate", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@registrationDate"].Value = aStudent.registrationDate;
                saved = GetSqlCommand.ExecuteNonQuery();
                if (saved > 0)
                {
                    saveDoneInformation = true;
                }
                GetSqlConn.Close();
                saveDoneEducation = SaveEducation(registrationNo, aStudentEducation);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            if(saveDoneEducation && saveDoneInformation)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        private bool SaveEducation(string registrationNo, List<StudentEducation> aStudentEducation)
        {
            bool saveDoneEducation = false;
            int saveAll = 0;
            try
            {
                foreach (StudentEducation studentEducation in aStudentEducation)
                {
                    GetSqlConn.Open();
                    commandString = string.Format("INSERT INTO t_Student_education(registration_no,exam_name,grade_letter,cgpa,certificate)VALUES(@registrationNo,@examName,@gradeLetter,@cgpa,@certificate)");
                    GetSqlCommand.CommandText = commandString;
                    GetSqlCommand.Parameters.Clear();
                    GetSqlCommand.Parameters.Add("@registrationNo", SqlDbType.VarChar);
                    GetSqlCommand.Parameters["@registrationNo"].Value = registrationNo;
                    GetSqlCommand.Parameters.Add("@examName", SqlDbType.VarChar);
                    GetSqlCommand.Parameters["@examName"].Value = studentEducation.ExamName;
                    GetSqlCommand.Parameters.Add("@gradeLetter", SqlDbType.VarChar);
                    GetSqlCommand.Parameters["@gradeLetter"].Value = studentEducation.GradeLetter;
                    GetSqlCommand.Parameters.Add("@cgpa", SqlDbType.VarChar);
                    GetSqlCommand.Parameters["@cgpa"].Value = studentEducation.CGPA;
                    GetSqlCommand.Parameters.Add("@certificate", SqlDbType.VarChar);
                    GetSqlCommand.Parameters["@certificate"].Value = studentEducation.Certificate;
                    saveAll = GetSqlCommand.ExecuteNonQuery();
                    if (saveAll > 0)
                    {
                        saveDoneEducation = true;
                        saveAll = 0;
                    }
                    else
                    {
                        saveDoneEducation = false;
                    }
                    GetSqlConn.Close();
                }
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return saveDoneEducation;

        }

        public Student SearchAstudent(string regNo)
        {
            Student aStudent = new Student();
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT * FROM t_Student WHERE registration_no = @regNo");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@regNo", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@regNo"].Value = regNo;
                getData = GetSqlCommand.ExecuteReader();
                while (getData.Read())
                {
                    aStudent.RegistrationNo = getData["registration_no"].ToString();
                    aStudent.name = getData["student_name"].ToString();
                    aStudent.email = getData["student_email"].ToString();
                    aStudent.departmentName = getData["student_department"].ToString();
                }
                GetSqlConn.Close();
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return aStudent;
        }

        
    }
}